AWR Instance Efficiency Percentages

Execute to Parse %: Shows how often parsed SQL statements are reused without reparsing.

As an efficiency rule: All the ratios should be above 90%

Interpreting the ratios in this section can be more complex than it appears. While high values for the ratios are generally good, indicating high efficiency, such values can be misleading as the system may be doing something efficiently that it would be better off not doing at all. Similarly, low values aren't always bad. For example, a low In-memory Sort ratio, indicating a low percentage of sorts performed in memory, would not necessarily be a cause for concern in a decision-support system (DSS) environment, where user response time is less critical than in an online transaction processing (OLTP) environment.

Basically, you need to keep in mind the characteristics of your application - whether it is query-intensive or update-intensive, whether it involves lots of sorting, and so on - when you're evaluating the Instance Efficiency Percentages.

It is possible for both the buffer hit ratio and the execute to parse ratios to be negative. In the case of the buffer hit ratio, the buffer cache is too small and the data in is being aged out before it can be used so it must be retrieved again. This is a form of thrashing which degrades performance immensely.

Buffer Nowait Ratio. This is the percentage of time that the instance made a call to get a buffer (all buffer types are included here) and that buffer was made available immediately (meaning it didn't have to wait for the buffer; hence, "Buffer Nowait").

If the ratio is low, then could be a (hot) block(s) being contended for that should be found in the Buffer Wait Statistics section of the report for more detail on which type of block is being contended for.

Buffer Hit Ratio. (also known as the buffer-cache hit ratio) Ideally more than 95 percent for an OLTP application. It shows the % of times a particular block was found in buffer cache instead of incurring the overhead of a physical I/O (reading from disk).

Although known as one of the most important statistics to evaluate, this ratio can sometimes be misleading. A low buffer hit ratio does not necessarily mean the cache is too small; it may be that potentially valid full-table scans are artificially reducing what is otherwise a good ratio.

Similarly, a high buffer hit ratio (say, 99%) normally indicates that the cache is adequately sized, but this assumption may not always be valid.

For example, frequently executed SQL statements that repeatedly refer to a small number of buffers via indexed lookups can create a misleadingly high buffer hit ratio.

When these buffers are read, they are placed at the most recently used (MRU) end of the buffer cache; iterative access to these buffers can artificially inflate the buffer hit ratio. This inflation makes tuning the buffer cache a challenge. Sometimes you can identify a too-small buffer cache by the appearance of the write complete waits event, which indicates that hot blocks (that is, blocks that are still being modified) are aging out of the cache while they are still needed; check the Wait Events list for evidence of this event.

Library Hit Ratio. This ratio, also known as the library-cache hit ratio, gives the percentage of pin requests that result in pin hits. A pin hit occurs when the SQL or PL/SQL code to be executed is already in the library cache and is valid to execute.

If the Library Hit ratio" is low, it could be indicative of:

If the soft parse ratio is also low:

Ceck whether there's a parsing issue. A lower ratio could also indicate that bind variables are not used or some other issue is causing SQL not to be reused (in which case a smaller shared pool may only be a band-aid that will potentially fix a library latch problem which may result).

Execute to Parse. The execute to parse ratio is basically a measure between the number of times a sql is executed versus the number of times it is parsed.

The execute to parse ratio should be very high in a ideal database. The ratio will move higher as the number of executes go up, while the number of parses either go down or remain the same. The ratio will be close to zero if the number of executes and parses are almost equal. The ratio will be negative executes are lower but the parses are higher.

If this value is negative, it means that the number of parses is larger than the number of executions.

Another cause for a negative execute to parse ratio is if the shared pool is too small and queries are aging out of the shared pool and need to be reparsed. This is another form of thrashing which also significantly degrades performance.

So, if you run some SQL and it has to be parsed every time you execute it (because no plan exists for this statement) then your percentage would be 0%. The more times that your SQL statement can reuse an existing plan the higher your Execute to Parse ratio is.

One way to increase your parse ratio is to use bind variables.

Parse issues usually are a result of:

The only way to influence that number is to either change:

  1. The number of times you parse.
  2. The number of times you execute.

The formula used:

Execute to Parse % = round(100*(1-:prse/:exe),2) pctval

If the number of parse calls is near the number of execute calls, then this ratio drifts towards zero (as yours is). As the number of execute calls increases (while holding parse calls constant), this number drifts towards 100%. That means you have parsed a statement ONCE and executed it MANY TIMES (that is good, that is best)

The Average Execute to Parse % of 57.87 shows that on average 42.13% of the SQL statement are parsed before being executed. This behavior is indicative of the application not entirely using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like significant parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

Parse CPU to Parse Elapsd %: Generally, this is a measure of how available your CPU cycles were for SQL parsing. If this is low, you may see "latch free" as one of your top wait events.

What may be the possible reasons for such low ratio?

This "Execute to Parse ratio" actually tells many things and seeks many areas to look into for further information. Causes could be one of the followings or something else -

  1. There might be no "Prepared Statement caching" in java layer or in jdbc connection pool
  2. There might be the case, before the execution of a query, the cursor was closed
  3. There might not be enough "session cached cursors" to hold the queries to reuse
  4. There might be the case where new queries were coming to play quite frequently
  5. There might be connection interruptions

The ratio is defined using the following formula: Execute to Parse Ratio = ( execute count - parse count total ) / execute count where parse count total - total number of parses (soft + hard) from v$sysstat.VALUE 'parse count (total)'; execute count - number of SQL executions from v$sysstat.VALUE 'execute count'.

Redo Nowait Ratio. This ratio indicates the amount of redo entries generated for which there was space available in the redo log.

The instance didn't have to wait to use the redo log if this is 100%. The redo-log space-request statistic is incremented when an Oracle process attempts to write a redo-log entry but there is not sufficient space remaining in the online redo log. Thus, a value close to 100 percent for the redo nowait ratio indicates minimal time spent waiting for redo logs to become available, either because the logs are not filling up very often or because the database is able to switch to a new log quickly whenever the current log fills up.

In-Memory Sort Ratio. This ratio gives the percentage of sorts that were performed in memory, rather than requiring a disk-sort segment to complete the sort.

Optimally, in an OLTP environment, this ratio should be high. Setting the PGA_AGGREGATE_TARGET (or SORT_AREA_SIZE) initialization parameter effectively will eliminate this problem, as a minimum you pretend to have this one in 95%

Soft Parse Ratio. This ratio gives the percentage of parses that were soft, as opposed to hard.

A soft parse occurs when a session attempts to execute a SQL statement and a usable version of the statement is already in the shared pool. In other words, all data (such as the optimizer execution plan) pertaining to the statement in the shared pool is equally applicable to the statement currently being issued.

A hard parse, on the other hand, occurs when the current SQL statement is either not in the shared pool or not there in a shareable form.

An example of the latter case would be when the SQL statement in the shared pool is textually identical to the current statement but the tables referred to in the two statements resolve to physically different tables. Hard parsing is an expensive operation and should be kept to a minimum in an OLTP environment.

The aim is to parse once, execute many times.

Ideally, the soft parse ratio should be greater than 95 percent. When the soft parse ratio falls much below 80 percent, investigate whether you can share SQL by using bind variables or force cursor sharing by using the init.ora parameter cursor_sharing.

Before you jump to any conclusions about your soft parse ratio, however, be sure to compare it against the actual hard and soft parse rates shown in the Load Profile.

If the rates are low (for example, 1 parse per second), parsing may not be a significant issue in your system. Another useful standard of comparison is the proportion of parse time that was not CPU-related, given by the following ratio:

(parse time CPU) / (parse time elapsed)

A low value for this ratio could mean that the non-CPU-related parse time was spent waiting for latches, which might indicate a parsing or latching problem.

To investigate further, look at the shared-pool and library-cache latches in the Latch sections of the report for indications of contention on these latches.

Latch Hit Ratio. This is the ratio of the total number of latch misses to the number of latch gets for all latches.

A low value for this ratio indicates a latching problem, whereas a high value is generally good.

However, as the data is rolled up over all latches, a high latch hit ratio can artificially mask a low get rate on a specific latch. Cross-check this value with the Top 5 Wait Events to see if latch free is in the list, and refer to the Latch sections of the report.

Latch Hit % of less than 99 percent is usually a big problem.

Also check the "Shared Pool Statistics", if the "End" value is in the high 95%-100% range, this is a indication that the shared pool needs to be increased (especially if the "Begin" value is much smaller)